import common
import DCOracle2
import unittest

class test_stored_procedures(common.TestCase):

    def test_describe(self):
        
        d = self.db.describe('emp_actions')

        self.assertEqual(
            self.db.decodedesc(d),
            self.username+'.EMP_ACTIONS package '+\
            '\n\tEMPCUR procedure '
            '\n\t\tCSR argument  OUT REF CURSOR'
            '\n\tFIND function returns VARCHAR2 '
            '\n\t\tEMPID argument  IN NUMBER'
            '\n\tFINDEMPBYNAME procedure '
            '\n\t\tNAME argument  IN VARCHAR2'
            '\n\t\tID argument  OUT NUMBER'
            '\n\tFINDEMPBYNAMEI function returns NUMBER '
            '\n\t\tNAME argument  IN VARCHAR2'
            '\n\tFINDMGR procedure '
            '\n\t\tMGRID argument  IN NUMBER'
            '\n\t\tCUR argument  IN OUT REF CURSOR'
            '\n\tFINDP procedure '
            '\n\t\tEMPID argument  IN NUMBER'
            '\n\t\tEMPNAME argument  OUT VARCHAR2'
            '\n\tFINDR procedure '
            '\n\t\tEMPID argument  IN NUMBER'
            '\n\t\tMGRID argument  IN OUT NUMBER'
            '\n\tFINDY procedure '
            '\n\t\tEMPID argument  IN NUMBER'
            '\n\t\tEMPNAME argument  OUT VARCHAR2'
            '\n\t\tEJOB argument  IN OUT VARCHAR2'
            '\n\tNUM procedure '
            '\n\t\tVALUE argument  IN OUT NUMBER'
            '\n\tRETURNTAB procedure '
            '\n\t\tPOEM argument  OUT TABLE OF VARCHAR2'
            '\n\tTAKETAB procedure '
            '\n\t\tIDS argument  IN TABLE OF VARCHAR2'
            '\n\tTESTI procedure '
            '\n\t\tNAME argument  IN VARCHAR2'
            '\n\t\tID argument  IN NUMBER'
            )

        self.assertEqual(
            self.db.collapsedesc(d),
            [self.username,
              'EMP_ACTIONS',
              'package',
              [['',
                   'EMPCUR',
                   'procedure',
                   [['', 'CSR', 'argument', (102, 0, 0, 0, 0, ('OUT',))]]],
                 ['',
                     'FIND',
                     'function',
                     [['', None, 'argument', (1, 0, 0, 0, 0, ('OUT',))],
                          ['', 'EMPID', 'argument', (2, 22, 38, 0, 0, ('IN',))]]],
                 ['',
                     'FINDEMPBYNAME',
                     'procedure',
                     [['', 'NAME', 'argument', (1, 0, 0, 0, 0, ('IN',))],
                          ['', 'ID', 'argument', (2, 22, 0, 0, 0, ('OUT',))]]],
                 ['',
                     'FINDEMPBYNAMEI',
                     'function',
                     [['', None, 'argument', (2, 22, 38, 0, 0, ('OUT',))],
                          ['', 'NAME', 'argument', (1, 0, 0, 0, 0, ('IN',))]]],
                 ['',
                     'FINDMGR',
                     'procedure',
                     [['', 'MGRID', 'argument', (2, 22, 38, 0, 0, ('IN',))],
                          ['', 'CUR', 'argument', (102, 0, 0, 0, 0, ('IN', 'OUT'))]]],
                 ['',
                     'FINDP',
                     'procedure',
                     [['', 'EMPID', 'argument', (2, 22, 38, 0, 0, ('IN',))],
                          ['', 'EMPNAME', 'argument', (1, 0, 0, 0, 0, ('OUT',))]]],
                 ['',
                     'FINDR',
                     'procedure',
                     [['', 'EMPID', 'argument', (2, 22, 38, 0, 0, ('IN',))],
                          ['', 'MGRID', 'argument', (2, 22, 38, 0, 0, ('IN', 'OUT'))]]],
                 ['',
                     'FINDY',
                     'procedure',
                     [['', 'EMPID', 'argument', (2, 22, 38, 0, 0, ('IN',))],
                          ['', 'EMPNAME', 'argument', (1, 0, 0, 0, 0, ('OUT',))],
                          ['', 'EJOB', 'argument', (1, 0, 0, 0, 0, ('IN', 'OUT'))]]],
                 ['',
                     'NUM',
                     'procedure',
                     [['', 'VALUE', 'argument', (2, 22, 0, 0, 0, ('IN', 'OUT'))]]],
                 ['',
                     'RETURNTAB',
                     'procedure',
                     [['',
                            'POEM',
                            'argument',
                            (251,
                                   0,
                                   0,
                                   0,
                                   0,
                                   ('OUT',),
                                   ['', None, 'argument', (1, 20, 0, 0, 0, ('OUT',))])]]],
                 ['',
                     'TAKETAB',
                     'procedure',
                     [['',
                            'IDS',
                            'argument',
                            (251,
                                   0,
                                   0,
                                   0,
                                   0,
                                   ('IN',),
                                   ['', None, 'argument', (1, 20, 0, 0, 0, ('IN',))])]]],
                 ['',
                     'TESTI',
                     'procedure',
                     [['', 'NAME', 'argument', (1, 0, 0, 0, 0, ('IN',))],
                          ['', 'ID', 'argument', (2, 22, 38, 0, 0, ('IN',))]]]]]
            )

    def test_describe2(self):

        self.assertEqual(
            self.db.collapsedesc(self.db.describe('emp')),
            [self.username,
              'EMP',
              'table',
              [['', 'EMPNO', 'column', (2, 22, 4, 0, 0, ())],
                 ['', 'ENAME', 'column', (1, 10, 0, 0, 1, ())],
                 ['', 'JOB', 'column', (1, 9, 0, 0, 1, ())],
                 ['', 'MGR', 'column', (2, 22, 4, 0, 1, ())],
                 ['', 'HIREDATE', 'column', (12, 7, 0, 0, 1, ())],
                 ['', 'SAL', 'column', (2, 22, 7, 2, 1, ())],
                 ['', 'COMM', 'column', (2, 22, 7, 2, 1, ())],
                 ['', 'DEPTNO', 'column', (2, 22, 2, 0, 1, ())]]]
            )

    def test_mapproc(self):

        self.assertEqual(
            self.db.mapproc('emp_actions'),
            {'EMP_ACTIONS.RETURNTAB': [
              '',
              'RETURNTAB',
              'procedure', [
                ['',
                 'POEM',
                 'argument',
                 (251, 0, 0, 0, 0, ('OUT',),
                  ['', None, 'argument', (1, 20, 0, 0, 0, ('OUT',))])]
                ]
              ],
             'EMP_ACTIONS.FINDR': [
              '',
              'FINDR',
              'procedure', [
                ['',
                 'EMPID',
                 'argument',
                 (2, 22, 38, 0, 0, ('IN',))],
                ['',
                 'MGRID',
                 'argument',
                 (2, 22, 38, 0, 0, ('IN', 'OUT'))]
                ]
              ],
             'EMP_ACTIONS.FINDY': [
              '',
              'FINDY',
              'procedure', [
                ['',
                 'EMPID',
                 'argument',
                 (2, 22, 38, 0, 0, ('IN',))],
                ['',
                 'EMPNAME',
                 'argument',
                 (1, 0, 0, 0, 0, ('OUT',))],
                ['',
                 'EJOB',
                 'argument',
                 (1, 0, 0, 0, 0, ('IN', 'OUT'))]
                ]
              ],
             'EMP_ACTIONS.FINDEMPBYNAMEI': [
              '',
              'FINDEMPBYNAMEI',
              'function', [
                ['',
                 None,
                 'argument',
                 (2, 22, 38, 0, 0, ('OUT',))],
                ['',
                 'NAME',
                 'argument',
                 (1, 0, 0, 0, 0, ('IN',))]
                ]
              ],
             'EMP_ACTIONS': [
            self.username,
            'EMP_ACTIONS',
            'package', [
              ['',
               'EMPCUR',
               'procedure', [
                ['',
                 'CSR',
                 'argument',
                 (102, 0, 0, 0, 0, ('OUT',))]
                ]
               ],
              ['',
               'FIND',
               'function', [
                ['',
                 None,
                 'argument',
                 (1, 0, 0, 0, 0, ('OUT',))],
                ['',
                 'EMPID',
                 'argument',
                 (2, 22, 38, 0, 0, ('IN',))]
                ]
               ],
              ['',
               'FINDEMPBYNAME',
               'procedure', [
                ['',
                 'NAME',
                 'argument',
                 (1, 0, 0, 0, 0, ('IN',))],
                ['',
                 'ID',
                 'argument',
                 (2, 22, 0, 0, 0, ('OUT',))]
                ]
               ],
              ['',
               'FINDEMPBYNAMEI',
               'function', [
                ['',
                 None,
                 'argument',
                 (2, 22, 38, 0, 0, ('OUT',))],
                ['',
                 'NAME',
                 'argument',
                 (1, 0, 0, 0, 0, ('IN',))]
                ]
               ],
              ['',
               'FINDMGR',
               'procedure', [
                ['',
                 'MGRID',
                 'argument',
                 (2, 22, 38, 0, 0, ('IN',))],
                ['',
                 'CUR',
                 'argument',
                 (102, 0, 0, 0, 0, ('IN', 'OUT'))]  #22, 38, 0, 0, ('IN', 'OUT'))]
                ]
               ],
              ['',
               'FINDP',
               'procedure', [
                ['',
                 'EMPID',
                 'argument',
                 (2, 22, 38, 0, 0, ('IN',))],
                ['',
                 'EMPNAME',
                 'argument',
                 (1, 0, 0, 0, 0, ('OUT',))]
                ]
               ],
              ['',
               'FINDR',
               'procedure', [
                ['',
                 'EMPID',
                 'argument',
                 (2, 22, 38, 0, 0, ('IN',))],
                ['',
                 'MGRID',
                 'argument',
                 (2, 22, 38, 0, 0, ('IN', 'OUT'))]
                ]
               ],
              ['',
               'FINDY',
               'procedure', [
                ['',
                 'EMPID',
                 'argument',
                 (2, 22, 38, 0, 0, ('IN',))],
                ['',
                 'EMPNAME',
                 'argument',
                 (1, 0, 0, 0, 0, ('OUT',))],
                ['',
                 'EJOB',
                 'argument',
                 (1, 0, 0, 0, 0, ('IN', 'OUT'))]
                ]
               ],
              ['',
               'NUM',
               'procedure', [
                ['',
                 'VALUE',
                 'argument',
                 (2, 22, 0, 0, 0, ('IN', 'OUT'))]
                ]
               ],
              ['',
               'RETURNTAB',
               'procedure', [
                ['',
                 'POEM',
                 'argument',
                 (251, 0, 0, 0, 0, ('OUT',),
                  ['',
                   None,
                   'argument',
                   (1, 20, 0, 0, 0, ('OUT',))])
                 ]
                ]
               ],
              ['',
               'TAKETAB',
               'procedure',
               [['',
                 'IDS',
                 'argument',
                 (251, 0, 0, 0, 0, ('IN',),
                  ['', None, 'argument', (1, 20, 0, 0, 0, ('IN',))])
                 ]
                ]],
              ['',
               'TESTI',
               'procedure',
               [['',
                 'NAME',
                 'argument',
                 (1, 0, 0, 0, 0, ('IN',))
                 ],
                ['', 'ID', 'argument', (2, 22, 38, 0, 0, ('IN',))]]]
              ]],
             'EMP_ACTIONS.EMPCUR': [
               '',
               'EMPCUR',
               'procedure',
               [['', 'CSR', 'argument', (102, 0, 0, 0, 0, ('OUT',))]]],
             'EMP_ACTIONS.FINDMGR': [
               '',
               'FINDMGR',
               'procedure',
               [['', 'MGRID', 'argument', (2, 22, 38, 0, 0, ('IN',))],
                ['', 'CUR', 'argument', (102, 0, 0, 0, 0, ('IN', 'OUT'))]]],
             'EMP_ACTIONS.TESTI': [
               '',
               'TESTI',
               'procedure',
               [['', 'NAME', 'argument', (1, 0, 0, 0, 0, ('IN',))],
                ['', 'ID', 'argument', (2, 22, 38, 0, 0, ('IN',))]]],
             'EMP_ACTIONS.FINDEMPBYNAME': [
               '',
               'FINDEMPBYNAME',
               'procedure',
               [['', 'NAME', 'argument', (1, 0, 0, 0, 0, ('IN',))],
                ['', 'ID', 'argument', (2, 22, 0, 0, 0, ('OUT',))]]],
             'EMP_ACTIONS.NUM': [
               '',
               'NUM',
               'procedure',
               [['', 'VALUE', 'argument', (2, 22, 0, 0, 0, ('IN', 'OUT'))]]],
             'EMP_ACTIONS.FIND': [
               '',
               'FIND',
               'function',
               [['', None, 'argument', (1, 0, 0, 0, 0, ('OUT',))],
                ['', 'EMPID', 'argument', (2, 22, 38, 0, 0, ('IN',))]]],
             'EMP_ACTIONS.FINDP': [
               '',
               'FINDP',
               'procedure',
               [['', 'EMPID', 'argument', (2, 22, 38, 0, 0, ('IN',))],
                ['', 'EMPNAME', 'argument', (1, 0, 0, 0, 0, ('OUT',))]]],
             'EMP_ACTIONS.TAKETAB': [
               '',
               'TAKETAB',
               'procedure',
               [['',
                 'IDS',
                 'argument',
                 (251, 0, 0, 0, 0, ('IN',),
                  ['', None, 'argument', (1, 20, 0, 0, 0, ('IN',))])]]]}
            )

    def test_callproc(self):
        
        find = self.db.procedure.emp_actions.find

        self.failUnless(
            isinstance(find,
                       DCOracle2.DCOracle2.procedure)
            )

        self.assertEqual(
            dir(find),
            #['__doc__', '__name__', '_argList', '_cursor', '_description', '_exec', '_schema']            
            ['__call__', '__doc__', '__getattr__', '__init__', '__module__', '__name__', '_argList', '_argconvertIn', '_argconvertOut', '_cursor', '_description', '_exec', '_schema', '_size', 'argList']
            )

        self.assertEqual(find(7902),'FORD')

        self.assertEqual(self.db.procedure.emp_actions.findp(7902),'FORD')

    def test_callproc2(self):
        
        findy = self.db.procedure.emp_actions.findy
        self.assertEqual(findy(7902,"Manager"),['FORD','ANALYST'])

    def test_doc1(self):
        
        self.assertEqual(
            getattr(self.c.procedure,self.username.lower()).emp_actions.find.__doc__,
            'function %s.EMP_ACTIONS.FIND returns OUT VARCHAR2, has arguments:'
            '\n\tEMPID IN NUMBER\n' % self.username
            )

    def test_doc2(self):

        self.assertEqual(
            getattr(self.c.procedure,self.username.lower()).emp_actions.__doc__,
            'package %s.EMP_ACTIONS contains:'
            '\n\tprocedure EMPCUR has arguments:'
            '\n\t\tCSR OUT REF CURSOR'
            '\n\tfunction FIND returns OUT VARCHAR2, has arguments:'
            '\n\t\tEMPID IN NUMBER'
            '\n\tprocedure FINDEMPBYNAME has arguments:'
            '\n\t\tNAME IN VARCHAR2'
            '\n\t\tID OUT NUMBER'
            '\n\tfunction FINDEMPBYNAMEI returns OUT NUMBER, has arguments:'
            '\n\t\tNAME IN VARCHAR2'
            '\n\tprocedure FINDMGR has arguments:'
            '\n\t\tMGRID IN NUMBER'
            '\n\t\tCUR IN OUT REF CURSOR'
            '\n\tprocedure FINDP has arguments:'
            '\n\t\tEMPID IN NUMBER'
            '\n\t\tEMPNAME OUT VARCHAR2'
            '\n\tprocedure FINDR has arguments:'
            '\n\t\tEMPID IN NUMBER'
            '\n\t\tMGRID IN OUT NUMBER'
            '\n\tprocedure FINDY has arguments:'
            '\n\t\tEMPID IN NUMBER'
            '\n\t\tEMPNAME OUT VARCHAR2'
            '\n\t\tEJOB IN OUT VARCHAR2'
            '\n\tprocedure NUM has arguments:'
            '\n\t\tVALUE IN OUT NUMBER'
            '\n\tprocedure RETURNTAB has arguments:'
            '\n\t\tPOEM OUT TABLE OF T VARCHAR2'
            '\n\n\tprocedure TAKETAB has arguments:'
            '\n\t\tIDS IN TABLE OF  VARCHAR2'
            '\n\n\tprocedure TESTI has arguments:'
            '\n\t\tNAME IN VARCHAR2'
            '\n\t\tID IN NUMBER\n' % self.username
            )

if __name__ == '__main__':
    unittest.main()

